package com.csw.mysqldate.controller;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

public class TwoExportSuperRecommend {

	public static void main(String[] args) {
		cascade();
	}

	public static void cascade() {
		String filePath = "D:/workbook.xls";
		int maxLine = 9999;
		// 创建一个excel
		@SuppressWarnings("resource")
		Workbook workbook = new XSSFWorkbook();

		// 创建需要用户填写的sheet
		XSSFSheet sheetzs = (XSSFSheet) workbook.createSheet("省市县");
		Row rowzs = sheetzs.createRow(0);
		String[] titlezs = {"新下拉", "老下拉", "跟老动", "跟老展示", "跟新展示"};
		Cell cellzs;
		for (int i = 0; i < titlezs.length; i++) {
			cellzs = rowzs.createCell(i + 4);
			cellzs.setCellValue(titlezs[i]);
		}
		for (int i = 1; i < maxLine; i++) {
			sheetzs.createRow(i);
		}
		//得到第一级省名称，放在列表里
		String[] firstxlk = {"字母", "数字"};
		//依次列出各省的市、各市的县
		String[] ziMu = {"abcdefg", "hijklmn", "opq", "rst", "uvwxyz"};
		String[] shuZi = {"12345", "金木水火土"};
		//将有子区域的父区域放到一个数组中
		String[] areaFatherNameArr = {"字母", "数字"};
		Map<String, String[]> areaMap = new HashMap<>(16);
		areaMap.put("字母", ziMu);
		areaMap.put("数字", shuZi);

		//创建一个专门用来存放地区信息的隐藏sheet页

		//因此也不能在现实页之前创建，否则无法隐藏。
		String areaSheetName = "area";
		Sheet areaSheet = workbook.createSheet(areaSheetName);
		//这一行作用是将此sheet隐藏，功能未完成时注释此行,可以查看隐藏sheet中信息是否正确
		// workbook.setSheetHidden(workbook.getSheetIndex(areaSheetName), true);

		int rowId = 0;
		// 设置第一行，存省的信息
		Row provinceRow = areaSheet.createRow(rowId++);
		provinceRow.createCell(0).setCellValue("娓??");
		for (int i = 0; i < firstxlk.length; i++) {// firstLevel
			Cell row2 = provinceRow.createCell(i + 1);
			row2.setCellValue(firstxlk[i]);// firstLevel
		}
		// 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
		for (String key : areaFatherNameArr) {
			String[] son = areaMap.get(key);
			Row row1 = areaSheet.createRow(rowId++);
			row1.createCell(0).setCellValue(key);
			for (int j = 0; j < son.length; j++) {
				Cell cell0 = row1.createCell(j + 1);
				cell0.setCellValue(son[j]);
			}

			// 添加名称管理器,rowId从1开始
			String range = getRange(1, rowId, son.length);
			Name name = workbook.createName();
			// key
			String fName = key;
			String nName = fName.replace("（", "_").replace("(", "_");
			String lName = nName.replace("）", "_").replace(")", "_");
			name.setNameName(lName);
			String formula = areaSheetName + "!" + range;
			name.setRefersToFormula(formula);
		}
		/**
		 * 放入主表并设置有效性
		 */
		setDropdownToExcel(sheetzs, firstxlk, 1, maxLine, 5, 5);
		initFgsIndustrydata(workbook, sheetzs, firstxlk, 1, maxLine, 4, 4, areaSheetName);
		/**
		 *拼接同步判断表达式
		 */
		for (int i = 1; i < maxLine; i++) {
			Row row7 = sheetzs.getRow(i);
			int j = i + 1;
			String lineff = "F";
			String sStart = "IF(" + lineff + j + "=\"\",\"\"";
            for (String aa : areaFatherNameArr) {
                String fName = aa;
                String nName = fName.replace("（", "_").replace("(", "_");
                String n = nName.replace("）", "_").replace(")", "_");
                sStart += ",IF(" + lineff + j + "=\"" + n + "\",VLOOKUP(" + lineff + j + "," + n + ",1)";
            }
            sStart += ",";
            for (int m = 0; m < areaFatherNameArr.length + 1; m++) {
                sStart += ")";
            }
            //sStart]" + sStart);
            row7.createCell(7).setCellFormula(sStart);
        }
		/**
		 *拼接同步判断表达式
		 */
		for (int i = 1; i < maxLine; i++) {
			Row row7 = sheetzs.getRow(i);
			int j = i + 1;
			String lineff = "E";
			String sStart = "IF(" + lineff + j + "=\"\",\"\"";
            for (String aa : areaFatherNameArr) {
                String fName = aa;
                String nName = fName.replace("（", "_").replace("(", "_");
                String n = nName.replace("）", "_").replace(")", "_");
                sStart += ",IF(" + lineff + j + "=\"" + n + "\",VLOOKUP(" + lineff + j + "," + n + ",2)";
            }
            sStart += ",";
            for (int m = 0; m < areaFatherNameArr.length + 1; m++) {
                sStart += ")";
            }
            //sStart]" + sStart);
            row7.createCell(8).setCellFormula(sStart);
        }

		//二级联动设置有效性
		for (int i = 2; i < maxLine; i++) {
			setDataValidation("F", sheetzs, i, 7);
		}

		FileOutputStream os = null;
		try {
			os = new FileOutputStream(new File(filePath));
			workbook.write(os);
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			IOUtils.closeQuietly(os);
		}
	}

	/**
	 * 破除255字符限制
	 */
	private static void initFgsIndustrydata(Workbook wk, Sheet sheet, String[] nameList, int firstRow, int lastRow,
											int firstCol, int lastCol, String sheetName) {
		Name namedCell = wk.createName();
		String nameName = sheetName + "xlk";
		namedCell.setNameName(nameName);
		//引用列，industry为上面创建一张隐藏掉sheet表,$A$2:$A$n,是此表中存放掉数据
		String refersToFormula = sheetName + "!$A$2:$A$" + (nameList.length + 1);
		namedCell.setRefersToFormula(refersToFormula);
		// 四个参数分别是：起始行、终止行、起始列、终止列
		CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
		DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
		//验证约束
		DataValidationConstraint dataValidationConstraint = dataValidationHelper.createFormulaListConstraint(nameName);
		//增加数据校验
		DataValidation provinceDataValidation = dataValidationHelper.createValidation(dataValidationConstraint,
				regions);
		//处理Excel兼容性问题
		if (provinceDataValidation instanceof XSSFDataValidation) {
			provinceDataValidation.setSuppressDropDownArrow(true);
			provinceDataValidation.setShowErrorBox(true);
		} else {
			provinceDataValidation.setSuppressDropDownArrow(false);
		}
		sheet.addValidationData(provinceDataValidation);
	}

	public static void setDataValidation(String offset, XSSFSheet sheet, int rowNum, int colNum) {
		XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
		DataValidation dataValidation;
		dataValidation = getDataValidationByFormula("INDIRECT($" + offset + (rowNum) + ")", rowNum, colNum, dvHelper);
		sheet.addValidationData(dataValidation);
	}

	/**
	 * 有255字符限制
	 *
	 * @param sheet
	 * @param strs
	 * @param firstRow
	 * @param lastRow
	 * @param firstCol
	 * @param lastCol
	 */
	private static void setDropdownToExcel(Sheet sheet, String[] strs, int firstRow, int lastRow, int firstCol,
										   int lastCol) {
		CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
		DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
		DataValidationConstraint createExplicitListConstraint = dataValidationHelper.createExplicitListConstraint(strs);
		DataValidation createValidation = dataValidationHelper.createValidation(createExplicitListConstraint, regions);

		if (createValidation instanceof XSSFDataValidation) {
			createValidation.setSuppressDropDownArrow(true);
			createValidation.setShowErrorBox(true);
		} else {
			createValidation.setSuppressDropDownArrow(false);
		}
		sheet.addValidationData(createValidation);
	}

	/**
	 * 加载下拉列表内容
	 *
	 * @param formulaString
	 * @param naturalRowIndex
	 * @param naturalColumnIndex
	 * @param dvHelper
	 * @return
	 */
	private static DataValidation getDataValidationByFormula(String formulaString, int naturalRowIndex,
															 int naturalColumnIndex, XSSFDataValidationHelper dvHelper) {
		// 加载下拉列表内容

		// 举例：若formulaString = "INDIRECT($A$2)" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据，

		//如果A2是江苏省，那么此处就是江苏省下的市信息。
		XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
				.createFormulaListConstraint(formulaString);
		// 设置数据有效性加载在哪个单元格上。

		// 四个参数分别是：起始行、终止行、起始列、终止列
		int firstRow = naturalRowIndex - 1;
		int lastRow = naturalRowIndex - 1;
		int firstCol = naturalColumnIndex - 1;
		int lastCol = naturalColumnIndex - 1;
		CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
		// 数据有效性对象

		// 绑定
		XSSFDataValidation dataValidationList = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
		dataValidationList.setEmptyCellAllowed(false);
		if (dataValidationList instanceof XSSFDataValidation) {
			dataValidationList.setSuppressDropDownArrow(true);
			dataValidationList.setShowErrorBox(true);
		} else {
			dataValidationList.setSuppressDropDownArrow(false);
		}
		// 设置输入信息提示信息
		dataValidationList.createPromptBox("涓???????ず", "璇蜂娇?ㄤ????寮??????????硷?");
	/*	data_validation_list.createErrorBox("选择错误提示", "你输入的值未在备选列表中，请下拉选择合适的值！");*/
		return dataValidationList;
	}

	/**
	 * 计算formula
	 *
	 * @param offset   偏移量，如果给0，表示从A列开始，1，就是从B列
	 * @param rowId    第几行
	 * @param colCount 一共多少列
	 * @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
	 */
	public static String getRange(int offset, int rowId, int colCount) {
		char start = (char) ('A' + offset);
		if (colCount <= 25) {
			char end = (char) (start + colCount - 1);
			return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;
		} else {
			char endPrefix = 'A';
			char endSuffix = 'A';
			if ((colCount - 25) / 26 == 0 || colCount == 51) {
				if ((colCount - 25) % 26 == 0) {// 杈圭???
					endSuffix = (char) ('A' + 25);
				} else {
					endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
				}
			} else {// 51浠ヤ?
				if ((colCount - 25) % 26 == 0) {
					endSuffix = (char) ('A' + 25);
					endPrefix = (char) (endPrefix + (colCount - 25) / 26 - 1);
				} else {
					endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
					endPrefix = (char) (endPrefix + (colCount - 25) / 26);
				}
			}
			return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;
		}
	}
}
